package com.lynn.salary.util;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SQLCreator {

	/**
	 * SQL 类型 select| insert| delete| update
	 */
	private String type;

	// private List<TableAndField> fieldList; //

	private Table table1;

	private Table table2;

	private List<String> conditions;

	private List<TableLink> tableLinks;

	private int ai = 0;

	private String alias[] = new String[] { "t1", "t2" };

	private List<Object> values;

	/**
	 * 主表
	 */
	private Class<?> mainTable;
	/**
	 * 字段列表，外部传递进来的，需要经过分析
	 */
	private String[] fieldArray;

	private Map<String, Object> data;

	private Object insertObject;

	public SQLCreator(String type, Class<?> mainTable, String[] fieldArray, String condition) {
		this.type = type;
		tableLinks = new ArrayList<TableLink>();
		conditions = new ArrayList<String>();
		this.mainTable = mainTable;
		this.fieldArray = fieldArray;
		addCondition(condition);
	}

	public SQLCreator(String type, Class<?> c, Map<String, Object> data) {
		this.type = type;
		this.mainTable = c;
		this.data = data;
	}

	public <T> SQLCreator(String type, T o) {
		this.type = type;
		this.mainTable = o.getClass();
		Map<String, Object> data = converObject2MapData(o);
		if (type.toUpperCase().equals("INSERT")) {
			insertObject = o;
		}
		this.data = data;
	}

	/**
	 * 将对象按属性-属性值的方式生成Map对象
	 * 
	 * @param o
	 * @return 返回转换结果Map
	 */
	private <T> Map<String, Object> converObject2MapData(T o) {
		Map<String, Object> data = new HashMap<String, Object>();
		Method getFields;
		String fields[] = null;
		try {
			getFields = mainTable.getMethod("getFields");
			fields = (String[]) getFields.invoke(o);
			for (String k : fields) {
				Method getMethod = mainTable.getMethod(ToolMethod.change2GetMethodName(k));
				Object v = getMethod.invoke(o);
				data.put(k, v);
			}
		} catch (Exception e) {
			System.err.println("SQLCreator::converObject2MapData error!");
			e.printStackTrace();
		}
		return data;
	}

	public SQLCreator(String type, Class<?> c, String condition) {
		this.type = type;
		this.mainTable = c;
		this.conditions = new ArrayList<String>();
		this.conditions.add(condition);
	}

	public void build() throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
		if (type.toUpperCase().equals("SELECT"))
			selectBuild();
	}

	private void updateBuild() {
		// update {tableName} set {filed1}={value1},... where {pk=pkValue}

	}

	private void selectBuild() throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {

		String tableName = _getTableName(mainTable);
		Map<String, Table> tmpTableFields = new HashMap<String, Table>();// 存储用到的所有表和字段信息

		Table tableAndField = new Table();
		tableAndField.setTableName(tableName);
		table1 = tableAndField;

		for (int i = 0; i < fieldArray.length; i++) {
			String fieldLable = fieldArray[i].replaceAll("\\s+", " ").trim();
			if (fieldLable.startsWith("$")) { // 如果这个字段使用了外键规则
												// 外键规则
												// ${fieldName}#{linkFieldName}
				StringBuffer tmp = new StringBuffer(fieldLable);
				String real = tmp.substring(1, tmp.indexOf("#"));

				// int blankIndex = tmp.indexOf(" ");
				// int endIndex = blankIndex == -1 ? tmp.length() : blankIndex;

				// String link = tmp.substring(tmp.indexOf("#") + 1, endIndex);
				String linkAndAlians = tmp.substring(tmp.indexOf("#") + 1);
				Object[] fk = _getFk(real);

				Class<?> linkTable = (Class<?>) fk[0];
				String linkTableName = _getTableName(linkTable);
				String linkField = (String) fk[1];

				Table tmpLinkTable = null;
				if (!tmpTableFields.containsKey(linkTableName)) {
					tmpLinkTable = new Table();
					tmpLinkTable.setTableName(linkTableName);
					table2 = tmpLinkTable;
					TableLink newTableLink = new TableLink(tableAndField, real, tmpLinkTable, linkField);
					addTableLink(newTableLink);
					tmpTableFields.put(linkTableName, tmpLinkTable);
				} else {
					tmpLinkTable = tmpTableFields.get(linkTableName);
				}
				tmpLinkTable.addField(linkAndAlians);

			} else {
				//
				tableAndField.addField(fieldLable);
			}
		}

		if (tableLinks.size() == 0) {
			TableLink newTableLink = new TableLink(tableAndField, null, null, null);
			tableLinks.add(newTableLink);
		}

	}

	/**
	 * @param real
	 *            字段名
	 * @return 返回该字段关联的表和属性名
	 * @throws NoSuchMethodException
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 */
	private Object[] _getFk(String real) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
		Object[] fk = null;
		Method getFk;
		try {
			getFk = mainTable.getMethod("getFk", new Class[] { String.class });
			fk = (Object[]) getFk.invoke(mainTable, real);
		} catch (NoSuchMethodException e) {
			System.err.println("SQLCreator::_getFk");
			e.printStackTrace();
			throw e;
		} catch (SecurityException e) {
			System.err.println("SQLCreator::_getFk");
			e.printStackTrace();
			throw e;
		} catch (IllegalAccessException e) {
			System.err.println("SQLCreator::_getFk");
			e.printStackTrace();
			throw e;
		} catch (IllegalArgumentException e) {
			System.err.println("SQLCreator::_getFk");
			e.printStackTrace();
			throw e;
		} catch (InvocationTargetException e) {
			System.err.println("SQLCreator::_getFk");
			e.printStackTrace();
			throw e;
		}
		return fk;
	}

	private String _getTableName(Class<?> c) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {

		Method getTableName;

		try {
			getTableName = c.getMethod("getTableName");
			String t = (String) getTableName.invoke(c);
			return t;
		} catch (NoSuchMethodException e) {
			System.out.println("SQLCreator::_getTableName error...");
			e.printStackTrace();
			throw e;
		} catch (SecurityException e) {
			System.out.println("SQLCreator::_getTableName error...");
			e.printStackTrace();
			throw e;
		} catch (IllegalAccessException e) {
			System.out.println("SQLCreator::_getTableName error...");
			e.printStackTrace();
			throw e;
		} catch (IllegalArgumentException e) {
			System.out.println("SQLCreator::_getTableName error...");
			e.printStackTrace();
			throw e;
		} catch (InvocationTargetException e) {
			System.out.println("SQLCreator::_getTableName error...");
			e.printStackTrace();
			throw e;
		}
	}

	public String createSQL() {
		String sql = null;
		if (type.toUpperCase().equals("SELECT")) {
			sql = createSelectSQL();
		} else if (type.toUpperCase().equals("UPDATE")) {
			sql = createUpdateSQL();
		} else if (type.toUpperCase().equals("INSERT")) {
			sql = createInsertSQL();
		} else if (type.toUpperCase().equals("DELETE")) {
			sql = createDeleteSQL();
		}

		/*
		 * StringBuffer sqlu = new StringBuffer(sql); int i = 0; while (i <
		 * sqlu.length()) { if ((sqlu.charAt(i) == ' ') && (i + 1 <
		 * sqlu.length()) && (sqlu.charAt(i + 1) == ' ')) { sqlu =
		 * sqlu.deleteCharAt(i + 1); } else i++; } System.out.println("SQL:" +
		 * sqlu); return sqlu.toString();
		 */
		return sql;
	}

	private String createDeleteSQL() {
		// delete from {tableName} where condition;
		StringBuffer sql = new StringBuffer("DELETE");
		sql.append(" FROM ");
		try {
			Method getTableName = mainTable.getMethod("getTableName");
			String tn = (String) getTableName.invoke(mainTable);
			sql.append(tn);
			sql.append(" WHERE ");
			sql.append(conditions.get(0));
		} catch (Exception e) {
			System.err.println("SQLCreator::createDeleteSQL出错");
			return null;
		}
		return sql.toString();
	}

	private String createInsertSQL() {
		// insert into {tableName} (field1,field2...) values (v1,v2...);
		StringBuffer sql = new StringBuffer("INSERT INTO ");
		String tableName = null;
		String pkName = null;
		try {
			if (insertObject == null) {
				insertObject = mainTable.newInstance();
			}
			Method getTableName = mainTable.getMethod("getTableName");
			tableName = (String) getTableName.invoke(mainTable);
			Method getPkName = mainTable.getMethod("getPkName");
			pkName = (String) getPkName.invoke(mainTable);
			sql.append(tableName);
			StringBuffer ks = new StringBuffer();
			StringBuffer vs = new StringBuffer();
			ks.append(" (");
			vs.append(" (");

			// 判断是否存在主键 没有则自动创建
			Object pk = data.get(pkName);
			if (pk == null || pk.toString().trim().equals("")) {
				pk = ToolMethod.autoId();
				data.put(pkName, pk);
				Method getMethod = mainTable.getMethod(ToolMethod.change2GetMethodName(pkName));
				Method setMethod = mainTable.getMethod(ToolMethod.change2SetMethodName(pkName), getMethod.getReturnType());
				setMethod.invoke(insertObject, pk);
			}
			values = new ArrayList<Object>();
			for (String k : data.keySet()) {
				ks.append(" ");
				ks.append(k);
				ks.append(",");

				Object v = data.get(k);
				vs.append(" ?");
				vs.append(" ,");

				values.add(v);
			}

			ks.deleteCharAt(ks.length() - 1);
			vs.deleteCharAt(vs.length() - 1);

			ks.append(")");
			vs.append(")");

			sql.append(ks);
			sql.append(" VALUES ");
			sql.append(vs);
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println("SQLCreator::createInsertSQL error...");
		}
		return sql.toString();
	}

	private String createUpdateSQL() {
		// update {tableName} set {filed1}={value1},... where {pk=pkValue}
		StringBuffer sql = new StringBuffer("UPDATE ");
		try {
			Method getTableName = mainTable.getMethod("getTableName");
			Method getPkName = mainTable.getMethod("getPkName");
			String tableName = (String) getTableName.invoke(mainTable);
			String pkName = (String) getPkName.invoke(mainTable);
			String condition = null;
			sql.append(tableName);
			sql.append(" SET ");
			if (data == null)
				throw new Exception();
			values = new ArrayList<Object>();
			for (String key : data.keySet()) {
				if (key.equals(pkName)) {
					condition = key + "=?";
					continue;
				}
				Object v = data.get(key);
				sql.append(" ");
				sql.append(key + "=?,");
				values.add(v);
			}
			sql.deleteCharAt(sql.length() - 1);
			sql.append(" WHERE ");
			if (condition == null) {
				System.err.println("SQLCreator::createUpdateSQL字段中不含主键出错");
				throw new Exception();
			}
			sql.append(condition);
			values.add(data.get(pkName));

		} catch (Exception e) {
			System.err.println("SQLCreator::createUpdateSQL出错");
			return null;
		}

		return sql.toString();
	}

	private String createSelectSQL() {
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append(getFields());
		sql.append(" FROM ");
		sql.append(getTableLinks());
		sql.append(" WHERE 1=1 ");
		sql.append(getConditions());
		return sql.toString();
	}

	private String getConditions() {
		StringBuffer conditions = new StringBuffer();
		for (String c : this.conditions) {
			conditions.append(" AND ");
			conditions.append(c);
			conditions.append(" ");
		}
		return conditions.toString();
	}

	private String getTableLinks() {
		StringBuffer links = new StringBuffer();
		boolean first = true;
		for (TableLink tl : tableLinks) {
			if (first) {
				first = false;
				if (tableLinks.size() == 1 && tl.getLinkTableField() == null) {
					links.append(tl.getTable().getTableName());
					links.append(" ");
					break;
				} else {
					links.append(tl.getTable().getTableName() + " AS " + tl.getTable().getTableAlias());
					links.append(" ");
				}
			}
			links.append(" LEFT JOIN " + tl.getLinkTable().getTableName() + " AS " + tl.getLinkTable().getTableAlias());
			links.append(" on ");
			links.append(tl.getTable().getTableAlias() + "." + tl.getTableField());
			links.append("=");
			links.append(tl.getLinkTable().getTableAlias() + "." + tl.getLinkTableField());
			links.append(" ");
		}

		return links.toString();
	}

	// private String getTables() {
	// if (fieldList.size() == 0) {
	// System.err.println("SQLCreator::getFields获取查询表为空出错");
	// return null;
	// }
	// StringBuffer tables = new StringBuffer();
	// boolean one = fieldList.size() == 0;
	// for (TableField tf : fieldList) {
	// if (one) {
	// tables.append(tf.getTableName() + " ");
	// } else {
	// tables.append(tf.getTableName() + " AS " + tf.getTableAlias()
	// + ",");
	// }
	// }
	// tables.deleteCharAt(tables.length() - 1);//
	// tables.append(" ");
	// return tables.toString();
	// }

	private String getFields() {
		StringBuffer fields = new StringBuffer();

		boolean useAlians = false;
		if (table2 != null) {
			useAlians = true;
		}

		Table[] twoTable = new Table[] { table1, table2 };
		for (Table tf : twoTable) {
			if (tf == null) {
				continue;
			}
			if (useAlians) {
				tf.setTableAlias(getNextAlias());
			}
			for (String f : tf.getFields()) {
				fields.append(" ");
				if (useAlians)
					fields.append(tf.getTableAlias() + "." + f);
				else
					fields.append(f);
				fields.append(",");
			}
		}

		fields.deleteCharAt(fields.length() - 1);
		return fields.toString();
	}

	public String getNextAlias() {
		return alias[ai++];
	}

	public void addTableLink(TableLink tl) {
		this.tableLinks.add(tl);
	}

	public void addCondition(String condition) {
		if (condition == null || condition.equals("")) {
			return;
		}
		this.conditions.add(condition);
	}

	public Object getInsertObject() {
		return insertObject;
	}

	public List<Object> getValues() {
		return values;
	}

}
